﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Secams.utils;
using System.Data.OleDb;
using ADODB;
using System.Data;
using Secams.model;

namespace Secams.controller
{
    class ParticipantDao
    {
        public const string TABLENAME = "datParticipant";
        public string _dbFilepath;

        public ParticipantDao(string dbFilepath)
        {
            if (dbFilepath != null)
                _dbFilepath = dbFilepath;
            else
                _dbFilepath = "../../data/Secams.mdb";
        }

        public bool initialize() {
            bool retval = false;
            using (OleDbConnection dbConn = MdbUtil.getConnection(_dbFilepath))
            {

                if (!MdbUtil.existsTable(dbConn, TABLENAME))
                {
                    //http://www.sulc.edu/sulcalumni/app/lib/pear/docs/MDB/doc/datatypes.html#5.1.3
                    OleDbCommand cmd = dbConn.CreateCommand();
                    cmd.CommandText = "CREATE TABLE " + TABLENAME + " (sid IDENTITY "
                        + ", registDt DATETIME null"
                        + ", id TEXT(32) not null"
                        + ", name TEXT(64) null"
                        + ", age INT null"
                        + ", gender TEXT(8) null"
                        + ", educationLevel TEXT(64) null"
                        + ", studyArea TEXT(64) null"
                        + ", remark TEXT(255) null"
                        + ", scoreTrain INT default 0"
                        + ", scoreTransfer INT default 0"
                        + ", lastTrialDt DATETIME null"
                        + ", CONSTRAINT  " + TABLENAME + "Constraint UNIQUE(id) );";
                    cmd.ExecuteNonQuery();
                    retval = true;
                }
                dbConn.Close();
            }

            return retval;

        }

        public int insertOrUpdate(ParticipantInfo info)
        {
            int retval = 0;
            using (OleDbConnection dbConn = MdbUtil.getConnection(_dbFilepath))
            {
                OleDbCommand sqlCommand = null;

                if (info.sid > 0) // update
                {
                    sqlCommand = new OleDbCommand(
                         "UPDATE " + TABLENAME + " SET age = @age, gender = @gender, educationLevel = @educationLevel, studyArea= @educationLevel, remark = @remark"
                         + " WHERE id = @id", dbConn);
                    sqlCommand.Parameters.Add("@id", info.id);
                }
                else // insert
                {
                    sqlCommand = new OleDbCommand(
                         "INSERT INTO " + TABLENAME + " (registDt, id, age, gender, educationLevel, studyArea, remark)"
                         + " values (@registDt, @id, @age, @gender, @educationLevel, @studyArea, @remark)", dbConn);
                    sqlCommand.Parameters.Add("@registDt", info.registDt.ToOADate());
                    sqlCommand.Parameters.Add("@id", info.id);
                    //insertCommand.Parameters.Add("@name", info.name);
                }

                sqlCommand.Parameters.Add("@age", info.age);
                sqlCommand.Parameters.Add("@gender", info.gender);
                sqlCommand.Parameters.Add("@educationLevel", info.educationLevel);
                sqlCommand.Parameters.Add("@studyArea", info.studyArea);
                sqlCommand.Parameters.Add("@remark", info.remark);

                /*
                insertCommand.Parameters["@id"].Value = info.id;
                //insertCommand.Parameters.Add("@name", info.name);
                insertCommand.Parameters["@age"].Value = info.age;
                insertCommand.Parameters["@gender"].Value = info.gender;
                insertCommand.Parameters["@educationLevel"].Value = info.educationLevel;
                insertCommand.Parameters["@studyArea"].Value = info.studyArea;
                insertCommand.Parameters["@remark"].Value = info.remark;
                insertCommand.Parameters["@registDt"].Value = info.registDt.ToOADate();
                 * */
                retval = sqlCommand.ExecuteNonQuery();
            }
            return retval;
        }

        public ParticipantInfo get(string id)
        {
            ParticipantInfo retval = null;
            using (OleDbConnection dbConn = MdbUtil.getConnection(_dbFilepath))
            {

                OleDbCommand qureyCommand = new OleDbCommand(
                    "SELECT sid, registDt, id, age, gender, educationLevel, studyArea, remark, scoreTrain, scoreTransfer, lastTrialDt FROM " + TABLENAME 
                     + " WHERE id= @id", dbConn);

                qureyCommand.Parameters.Add("@id", id);

                OleDbDataReader reader = qureyCommand.ExecuteReader();
                if (reader.HasRows)
                {
                    retval = new ParticipantInfo();

                    while (reader.Read())
                    {
                        retval.sid = (int)reader["sid"];
                        retval.registDt = (DateTime)reader["registDt"];
                        retval.id = (string)reader["id"];
                        //retval.name = reader["name"];
                        retval.age = (int)reader["age"];
                        retval.gender = (string)reader["gender"];
                        retval.educationLevel = (string)reader["educationLevel"];
                        retval.studyArea = (string)reader["studyArea"];
                        retval.remark = (string)reader["remark"];

                        retval.scoreTrain = (int)reader["scoreTrain"];
                        retval.scoreTransfer = (int)reader["scoreTransfer"];
                        object lastTrialDt = reader["lastTrialDt"];
                        if (!DBNull.Value.Equals(lastTrialDt))
                            retval.lastTrialDt = (DateTime)lastTrialDt;
                    }
                }
                reader.Close();
            }
            return retval;
        }

        public void updateScore(ParticipantInfo info)
        {
            using (OleDbConnection dbConn = MdbUtil.getConnection(_dbFilepath))
            {
                OleDbCommand insertCommand = new OleDbCommand(
                    "UPDATE " + TABLENAME + " SET scoreTrain = @scoreTrain, scoreTransfer = @scoreTransfer, lastTrialDt = @lastTrialDt"
                     +" WHERE id= @id", dbConn);

                insertCommand.Parameters.Add("@scoreTrain", info.scoreTrain);
                insertCommand.Parameters.Add("@scoreTransfer", info.scoreTransfer);
                insertCommand.Parameters.Add("@lastTrialDt", info.lastTrialDt);

                insertCommand.Parameters.Add("@id", info.id);

                insertCommand.ExecuteNonQuery();
            }
        }
    }
}
